﻿namespace IntellectShop.ExcelParser
{
	using System;
	using System.Collections.Generic;
	using System.IO;
	using System.Linq;
	using System.Text;
	using System.Text.RegularExpressions;
	using System.Xml;
	using ExcelLibrary.SpreadSheet;

	class Program
	{
		static readonly IDictionary<string, IEnumerable<string>> CategoryNames = new Dictionary<string, IEnumerable<string>>()
			{
				{"Ноутбуки", new string[]
								{
									"Notebooks"
									,"Продуктовая линейка: Асер"
									,"Продуктовая линейка: Асус"
									,"Продуктовая линейка: Hewlett-Packard Commercial"
									,"Продуктовая линейка: Hewlett-Packard Pavilion"
									,"Продуктовая линейка: Microstar"
									,"Продуктовая линейка: Фуджи"
								}},
				{"Комплектующие", new string[]
								{
									"Процессоры"
									,"Память оперативная"
									,"Память флеш"
									,"Материнские платы"
									,"Видеоадаптеры"
									,"Жёсткие диски"
									,"Оптические приводы"
									,"Корпуса, блоки питания и кулера"
									,"Флеш-память"
									,"Корпуса"
									,"Платформы Asrock, ASUS, Foxconn"
								}},
				{"Мультимедиа", new string[]
								{
									"Мультимедиа"
									,"Проекционное оборудование"
								}},
				{"Мониторы", new string[]
								{
									"Дисплеи LCD"
									,"Дисплеи Plasma"
									,"Интерактивные доски"
									,"Мониторы"
									,"Очки 3D"
									,"Телевизоры LCD"
									,"Телевизоры Plasma"
								}},
				{"Принтеры, МФУ, Сканеры", new string[]
								{
									"Personal ColorLaserJet Printers"
									,"Workgroup ColorLaserJet Printers"
									,"Personal LaserJet Printers"
									,"Workgroup LaserJet Printers"
									,"DesignJet Large Format Printers"
									,"RMA LFP (восстановленные)"
									,"InkJet All-In-Ones"
									,"Personal DeskJet Printers"
									,"Personal Photo Printers A4"
									,"Personal Mobile Photo Printers "
									,"Business InkJet Printers (A4)"
									,"Mobile InkJet Printers(A4)"
									,"PhotoSmart A3"
									,"Business InkJet Printers (A3)"
									,"Personal Scanners"
									,"Business Scanners (A3)"
									,"Business Scanners (A4)"
									,"LaserJet All in One"
									,"LaserJet All In One WorkCentre (A4)"
									,"LaserJet All In One WorkCentre (A3)"
									,"ColorLaserJet All in One"
									,"Color LaserJet All In One WorkCentre (A4)"
									,"Color LaserJet All In One WorkCentre (A3)"
									,"JetDirects"
									,"InkJet Accessories"
									,"Color LaserJet Accessories"
									,"LaserJet Accessories"
									,"Memory"
								}},
				{"Сетевое оборудование", new string[]
								{
									"Сетевое оборудование ASUS"
								}},
				{"Компьютеры", new string[]
								{
									"Компьютеры"
									,"Серверное оборудование"
									
								}},
				{"Аксессуары", new string[]
								{
									"Устройства  ввода  информации"
									,"Аксессуары"
								}},
				{"Расходные материалы", new string[]
								{
									"Paper for DesignJet"
									,"Paper for Laser"
									,"Paper for Ink"
									,"For Color Laser Jet Printer"
									,"For Laser Jet Printer"
									,"For DesignJet"
									,"For Inkjet Printer"
									,"Fuser, Transfer, Maint Kits; Drums"
								}},
				{"Прочее", new string[]
								{
									"Специальное предложение"
									,"Электронные книги"
									,"Оборудование в поврежденной упаковке"
									,"Прочее"
								}}
			};

		static readonly IEnumerable<string> VendorNames = new string[]
			{
				"ACBEL"
				,"ACER"
				,"ACORP"
				,"AMD"
				,"Apollo-T"
				,"ASRock"
				,"ASUS"
				,"ATI"
				,"Cooler Master"
				,"Compaq"
				,"ECS"
				,"Foxconn"
				,"Foxline"
				,"FSP"
				,"Gigabyte"
				,"HP"
				,"Intel"
				,"INWIN"
				,"Iomega"
				,"Kingston"
				,"Labtec"
				,"LG"
				,"Lifecam"
				,"Logitech"
				,"Microsoft"
				,"Microstar"
				,"MSI"
				,"NVIDIA"
				,"Palit"
				,"Philips"
				,"Pioneer"
				,"PNY"
				,"PocketBook"
				,"PowerColor"
				,"Samsung"
				,"Seagate"
				,"SONY"
				,"TakeMS"
				,"Thermaltake"
				,"TopView"
				,"Toshiba"
				,"Transcend"
				,"WD"
				,"ZOTAC"
			};

		static readonly IEnumerable<string> TechnologyNames = new string[]
			{
				"Wide"
				,"LED"
				,"LCD"
				,"Wireless"
				,"Laser"
				,"Optical"
				,"WiFi"
				,"USB"
				,"USB2.0"
				,"WLAN"
				,"ATX"
				,"mATX"
				,"mITX"
				,"ITX"
				,"IDE"
				,"SATA"
				,"SATAII"
				,"SATA3"
				,"GeForce"
				,"Radeon"
				,"AGP"
				,"Bluetooth"
				,"eMachines"
				,"EeePC"
				,"Atom"
				,"XGA"
				,"SVGA"
				,"VGA"
				,"Wired"
			};

		static string xmlFileName = "Hardwares.xml";
		static decimal rubUsd = 31.01m;

		static int Main(string[] argv)
		{
			if (argv.Length < 2)
			{
				Console.WriteLine(GetHelp());
				return 1;
			}

			Console.WriteLine("Начало работы.");
			Console.WriteLine();
			int itemsCount = 0;

			try
			{
				rubUsd = Decimal.Parse(argv[0]);
				Console.WriteLine("... курс доллара к рублю: {0}", rubUsd.ToString());

				XmlDocument doc = new XmlDocument();
				XmlElement root = doc.CreateElement("Hardwares");
				doc.AppendChild(root);

				for (int i = 1; i < argv.Length; i++)
				{
					string excelFileName = argv[i];
					Console.WriteLine();
					Console.WriteLine("... Excel-файл: {0}", excelFileName);

					using (FileStream fs = new FileStream(excelFileName, FileMode.Open))
					{
						Workbook workbook = Workbook.Load(fs);
						foreach (Worksheet worksheet in workbook.Worksheets)
						{
							Console.WriteLine("... обрабатываем страницу '{0}'", worksheet.Name);
							int worksheetItemsCount = 0;
							int cityColumnIndex = 0;
							int priceColumnIndex = 0;
							string category = String.Empty;
							string tags = String.Empty;
							for (int rowIndex = worksheet.Cells.FirstRowIndex; rowIndex <= worksheet.Cells.LastRowIndex; rowIndex++)
							{
								TryGetCityColumnIndex(worksheet.Cells.GetRow(rowIndex), ref cityColumnIndex);
								TryGetPriceColumnIndex(worksheet.Cells.GetRow(rowIndex), ref priceColumnIndex);

								Regex pat = new Regex(@"\d{10}");
								Match match = pat.Match(worksheet.Cells[rowIndex, 0].StringValue);
								//проверим - является ли строка товаром
								if (match.Success) //обычная запись о товаре с AXAPTA кодом
								{
									//берем только наш город
									if (String.IsNullOrEmpty(worksheet.Cells[rowIndex, cityColumnIndex].StringValue))
									{
										continue;
									}

									ParseHardware(
										doc,
										worksheet.Cells[rowIndex, 0].StringValue,
										worksheet.Cells[rowIndex, 1].StringValue,
										worksheet.Cells[rowIndex, 2].StringValue,
										worksheet.Cells[rowIndex, priceColumnIndex],
										category,
										tags);
									worksheetItemsCount++;
								}
								else
								{
									//какая-то информационная строка
									ParseCategoryAndInfo(worksheet.Cells.Rows[rowIndex], ref category, ref tags);
								}
							}

							Console.WriteLine("...... выявлено {0} строк с нужными товарами", worksheetItemsCount);
							itemsCount += worksheetItemsCount;
						}
					}

					using (XmlTextWriter writer = new XmlTextWriter(xmlFileName, Encoding.UTF8))
					{
						writer.Formatting = Formatting.Indented;
						writer.Indentation = 1;
						writer.IndentChar = '\t';
						doc.Save(writer);
					}
				}
			}
			catch (Exception ex)
			{
				Console.WriteLine("");
				Console.WriteLine(String.Format("Ошибка: {0}", ex.Message));
				return 1;
			}

			Console.WriteLine();
			Console.WriteLine("... всего выявлено {0} строк с нужными товарами", itemsCount);
			Console.WriteLine("... файл '{0}' успешно СОЗДАН.", xmlFileName);
			Console.WriteLine();
			Console.WriteLine("Конец работы.");

			return 0;
		}

		private static bool TryGetCityColumnIndex(Row row, ref int prevColIndex)
		{
			for (int indx = row.FirstColIndex; indx <= row.LastColIndex; indx++)
			{
				Cell cell = row.GetCell(indx);
				if (!cell.IsEmpty)
				{
					if (cell.StringValue.Equals("Чел-ск, свободно", StringComparison.InvariantCultureIgnoreCase) ||
						cell.StringValue.Equals("Чел-ск", StringComparison.InvariantCultureIgnoreCase) ||
						cell.StringValue.Equals("Чел", StringComparison.InvariantCultureIgnoreCase))
					{
						prevColIndex = indx;
						return true;
					}
				}
			}

			return false;
		}

		private static bool TryGetPriceColumnIndex(Row row, ref int prevColIndex)
		{
			for (int indx = row.FirstColIndex; indx <= row.LastColIndex; indx++)
			{
				decimal price;
				Cell cell = row.GetCell(indx);
				if (!cell.IsEmpty)
				{
					if (cell.Format.FormatType == CellFormatType.Currency ||
						cell.Format.FormatType == CellFormatType.Custom)
					{
						if (Decimal.TryParse(cell.StringValue, out price))
						{
							prevColIndex = indx;
							return true;
						}
					}
				}
			}

			return false;
		}

		private static void ParseCategoryAndInfo(Row row, ref string category, ref string tags)
		{
			Cell zeroCell = row.GetCell(0);
			List<string> allCategories = new List<string>();
			foreach (var key in CategoryNames.Keys)
			{
				allCategories.AddRange(CategoryNames[key]);
			}

			var query =
				from n in allCategories
				where String.Equals(n, zeroCell.StringValue, StringComparison.InvariantCultureIgnoreCase)
				select n;

			if (query.Any())
			{
				foreach (var key in CategoryNames.Keys)
				{
					if (CategoryNames[key].Contains(zeroCell.StringValue))
					{
						category = key;
						tags = zeroCell.StringValue;
						break;
					}
				}
			}
		}

		private static void ParseHardware(XmlDocument doc, string axaptaVal, string code, string name, Cell priceCell, string category, string tags)
		{
			XmlElement courseElement = doc.CreateElement("Hardware");
			doc.DocumentElement.AppendChild(courseElement);

			courseElement.SetAttribute("InternalCode", axaptaVal);
			courseElement.SetAttribute("Code", code.Length > 256 ? code.Substring(0, 256) : code);
			courseElement.SetAttribute("CategoryName", category);
			courseElement.SetAttribute("Name", name);
			courseElement.SetAttribute("Price", GetPrice(priceCell).ToString());
			courseElement.SetAttribute("Tags", tags);
			courseElement.SetAttribute("Description", String.Join(", ", GetTags(name)));
		}

		private static string[] GetTags(string name)
		{
			var query =
				from n in new string[] { name }
				from v in VendorNames.Union(TechnologyNames)
				where
					Regex.Match(n, @"(^|\s+|/|:|\(|,)(" + Regex.Escape(v) + @")(\s+|$|/|:|\)|,)", RegexOptions.Singleline | RegexOptions.IgnoreCase).Success
				select v;

			return query.ToArray();
		}

		private static decimal GetPrice(Cell priceCell)
		{
			decimal price = 0m;
			if (!String.IsNullOrEmpty(priceCell.StringValue) && priceCell.StringValue != "звоните")
			{
				if (priceCell.FormatString.Contains("р."))
				{
					Decimal.TryParse(priceCell.StringValue, out price);
				}
				else if (priceCell.FormatString.Contains("$"))
				{
					Decimal.TryParse(priceCell.StringValue, out price);
					price = Math.Round(price * rubUsd, 2);
				}
				else
				{
					Decimal.TryParse(priceCell.StringValue, out price);
				}
			}

			if (price == 0)
			{
				Console.WriteLine("......... ошибочная цена: value={0}, format={1}", priceCell.StringValue, priceCell.FormatString);
			}

			return price;
		}

		private static string GetHelp()
		{
			StringBuilder buf = new StringBuilder();
			buf.AppendLine(String.Empty);
			buf.AppendLine("Пример запуска программы:");
			buf.AppendFormat("{0} {1} price1.xls price2.xls price3.xls", Path.GetFileName(Environment.GetCommandLineArgs()[0]), 31.10m);
			buf.AppendLine(String.Empty);
			buf.AppendLine("Первый параметр: курс доллара к рублю.");
			buf.AppendLine("Со второго по последний параметр: имя Excel-файлов с товаром.");

			buf.AppendLine(String.Empty);
			return buf.ToString();
		}
	}
}
